Modify SSIS package’s ProtectionLevel and password using C#

Categories: Database; Tagged with: C# • DTSX • ETL • SSIS; @ December 9th, 2012 13:24

Requirement:

Modify SSIS DTSx package production level and password.

Solution:

It’s terrible to open and edit each package, luckily, we got the interface: Microsoft.SqlServer.Dts.Runtime. (How to add the lib)

      

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;

namespace SSISHelper.com.liguoliang.ssis.util
{
    class DtsUtils
    {
        /**
         * Change DTSX package password
         */
        public static void changePassword(String pkgLocation, String oldPassword, DTSProtectionLevel dtsPrdLevel, String newPassword)
        {
            Application app = new Application();

            if (oldPassword != null && oldPassword.Trim() != "")
            {
                app.PackagePassword = oldPassword;
            }

            Package pkg = app.LoadPackage(pkgLocation, null);

            // Modify the password
            pkg.ProtectionLevel = dtsPrdLevel;
            pkg.PackagePassword = newPassword;

            // Save dts pacakge
            app.SaveToXml(pkgLocation, pkg, null);
        }
    }
}

Get source code from GitHub: https://github.com/DavidGuoliang/SSISHelper/blob/master/SSISHelper/com/liguoliang/ssis/util/DtsUtils.cs

Besides password, there’re many interfaces, like logging, Connections, variables,.

Links:

1. SaveToXml: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetoxml.aspx

2. Building Packages Programmatically http://msdn.microsoft.com/en-us/library/ms345167.aspx

SSIS package Transaction Error: 0x8004D01B The Transaction Manager is not available <-> Google宣布不再提供免费版的Google Apps企业邮箱套件